package com.example.summertraining.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.summertraining.dto.*;
import com.example.summertraining.entity.Anjuke;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface AnjukeMapper extends BaseMapper<Anjuke> {

    @Select("select * from anjuke")
    List<Anjuke> selectAll();
    //房子布局
    @Select("SELECT SUBSTRING_INDEX(house, ' ', 4) as layout, count(*) as number FROM anjuke GROUP BY SUBSTRING_INDEX(house, ' ', 4);")
    List<houseBJ> houseBJ();

    //朝向
    @Select("SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(house, '㎡ ', -1), ' ', 1) as direction,count(*) as number FROM anjuke group by SUBSTRING_INDEX(SUBSTRING_INDEX(house, '㎡ ', -1), ' ', 1)")
    List<houseCX> houseCX();

    //建造时间
    @Select("SELECT SUBSTRING_INDEX(house, ' ', -1) as JZtime ,count(*) as number FROM anjuke where SUBSTRING_INDEX(house, ' ', -1) like '%建造'  group by SUBSTRING_INDEX(house, ' ', -1)")
    List<houseJZ> houseJZ();

    //楼层
    @Select("SELECT COUNT(*) AS number,'低层' AS floor FROM anjuke WHERE house LIKE '%低层%' UNION ALL\n" +
            "SELECT COUNT(*) AS number,'中层' AS floor FROM anjuke WHERE house LIKE '%中层%' UNION ALL\n" +
            "SELECT COUNT(*) AS number,'高层' AS floor FROM anjuke WHERE house LIKE '%高层%'\n")
    List<houseLC> houseLC();

    //长沙分布区块
    @Select("SELECT COUNT(*) AS number,'岳麓区' AS area FROM anjuke WHERE address LIKE '%岳麓%' UNION ALL\n" +
            "SELECT COUNT(*) AS number,'芙蓉区' AS area FROM anjuke WHERE address LIKE '%芙蓉%' UNION ALL\n" +
            "SELECT COUNT(*) AS number,'天心区' AS area FROM anjuke WHERE address LIKE '%天心%' UNION ALL\n" +
            "SELECT COUNT(*) AS number,'开福区' AS area FROM anjuke WHERE address LIKE '%开福%' UNION ALL\n" +
            "SELECT COUNT(*) AS number,'雨花区' AS area FROM anjuke WHERE address LIKE '%雨花%' UNION ALL\n" +
            "SELECT COUNT(*) AS number,'望城区' AS area FROM anjuke WHERE address LIKE '%望城%'")
    List<address> address();

    //周边环境
    @Select("SELECT COUNT(*) AS number,'近地铁' AS introduction FROM anjuke WHERE introduction LIKE '%近地铁%' UNION ALL\n" +
            "SELECT COUNT(*) AS number,'满五年' AS introduction FROM anjuke WHERE introduction LIKE '%满五年%' UNION ALL\n" +
            "SELECT COUNT(*) AS number,'绿化率高' AS introduction FROM anjuke WHERE introduction LIKE '%绿化率高%'")
    List<introduction> introduction();

    //特点
    @Select("SELECT COUNT(*) AS number,'急售' AS characteristic FROM anjuke WHERE name LIKE '%急售%' UNION ALL\n" +
            "SELECT COUNT(*) AS number,'精装' AS characteristic FROM anjuke WHERE name LIKE '%精装%' UNION ALL\n" +
            "SELECT COUNT(*) AS number,'电梯' AS characteristic FROM anjuke WHERE name LIKE '%电梯%'")
    List<nameTD> nameTD();

    //每平价格分区
    @Select("\n" +
            "SELECT \n" +
            "    CASE\n" +
            "        WHEN mprice >= 20000 THEN '20000万/平米以上'\n" +
            "        WHEN mprice BETWEEN 12000 AND 19999 THEN '12000万/平米-20000万/平米'\n" +
            "        WHEN mprice BETWEEN 8000 AND 11999 THEN '8000万/平米-12000万/平米'\n" +
            "        ELSE '8000万/平米以下'\n" +
            "    END AS mpriceRange,\n" +
            "    COUNT(*) AS count \n" +
            "FROM\n" +
            "    anjuke\n" +
            "GROUP BY\n" +
            "    mpriceRange" +
            ";")
    List<mprice> mprice();


    //房子总价区块数量
    @Select("SELECT   \n" +
            "    CASE   \n" +
            "        WHEN price < 80 THEN '80万以下'  \n" +
            "        WHEN price >= 80 AND price < 160 THEN   \n" +
            "            CASE   \n" +
            "                WHEN price < 100 THEN '80-99万'  \n" +
            "                WHEN price < 120 THEN '100-119万'  \n" +
            "                WHEN price < 140 THEN '120-139万'  \n" +
            "                WHEN price < 160 THEN '140-159万'   \n" +
            "            END  \n" +
            "        ELSE '160万以上'  \n" +
            "    END AS scoreRange,  \n" +
            "    COUNT(*) AS count  \n" +
            "FROM anjuke  \n" +
            "GROUP BY scoreRange  \n" +
            "ORDER BY   \n" +
            "    CASE   \n" +
            "        WHEN scoreRange = '80万以下' THEN 1  \n" +
            "        WHEN scoreRange = '80-99万' THEN 2  \n" +
            "        WHEN scoreRange = '100-119万' THEN 3  \n" +
            "        WHEN scoreRange = '120-139万' THEN 4  \n" +
            "        WHEN scoreRange = '140-159万' THEN 5  \n" +
            "        WHEN scoreRange = '160以上' THEN 6  \n" +
            "    END;  ")
    List<price> price();

}
